package com.kstech.ks_gaz01.manager

import android.content.Context
import android.content.res.AssetFileDescriptor
import android.os.Environment
import android.util.Log
import com.blankii.androidlibrary.exts.logd
import com.kstech.ks_gaz01.data.DebugDataBean
import com.kstech.ks_gaz01.getFileBasePath
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.DateUtil
import org.apache.poi.ss.usermodel.Row
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.usermodel.Workbook
import org.apache.poi.ss.usermodel.WorkbookFactory
import java.io.File
import java.io.FileDescriptor
import java.io.FileInputStream
import java.io.FileNotFoundException
import java.io.FileOutputStream
import java.io.IOException
import java.io.InputStream


/**
 * author: lenovo
 * created on: 2023/12/27 17:53
 * description:
 * POI文档：https://www.cnblogs.com/LiZhiW/p/4313789.html
 */
object ExcelManager {
    private val TAG: String = "ExcelManager"

    /**
     * 读取excel   （xls和xlsx）
     * @return
     */
    fun readExcel(context: Context, columns: Array<String>): List<Map<String?, String?>?>? {
        //1 通过assets 获取FileInputStream
        val assetFileDescriptor: AssetFileDescriptor = context.assets.openFd("123.xlsx")
        val fileDescriptor: FileDescriptor = assetFileDescriptor.fileDescriptor
        val stream = FileInputStream(fileDescriptor)
        //2 通过读取文件系统存储
        val logFilePath: String = Environment.getExternalStorageDirectory()
            .toString() + File.separator.toString() + "Visitor"
        val file = File(logFilePath, "test.xls")

        val filePath: String = file.absolutePath
        var sheet: Sheet? = null
        var row: Row? = null
        var rowHeader: Row? = null
        var list: MutableList<Map<String?, String?>?>? = null
        var cellData: String? = null
        var wb: Workbook? = null
        if (filePath == null) {
            return null
        }
        val extString = filePath.substring(filePath.lastIndexOf("."))
        var `is`: InputStream? = null
        try {
            `is` = FileInputStream(filePath)
            if (".xls" == extString) {
                wb = HSSFWorkbook(`is`)
            } else if (".xlsx" == extString) {
//                wb = XSS(`is`)
            } else {
                wb = null
            }
            if (wb != null) {
                // 用来存放表中数据
                list = ArrayList()
                // 获取第一个sheet
                sheet = wb.getSheetAt(0)
                // 获取最大行数
                val rownum = sheet.physicalNumberOfRows
                // 获取第一行
                rowHeader = sheet.getRow(0)
                row = sheet.getRow(0)
                // 获取最大列数
                val colnum: Int = row.physicalNumberOfCells
                for (i in 1 until rownum) {
                    val map: MutableMap<String?, String?> = LinkedHashMap()
                    row = sheet.getRow(i)
                    if (row != null) {
                        for (j in 0 until colnum) {
                            if (columns[j] == getCellFormatValue(rowHeader.getCell(j))) {
                                cellData = getCellFormatValue(
                                    row.getCell(j)
                                ) as String?
                                map[columns[j]] = cellData/*DecimalFormat df = new DecimalFormat("#");
                                System.out.println(    df.format(cellData));*/
//                                Log.e ("yy", "cellData=$cellData")
//                                Log.e("yy", "map=$map")
                            }
                        }
                    } else {
                        break
                    }
                    list.add(map)
                }
            }
        } catch (e: FileNotFoundException) {
            e.printStackTrace()
        } catch (e: IOException) {
            e.printStackTrace()
        }
        return list
    }

    private fun writeOneCell(sheet: Sheet, row: Int, cell: Int, value: String) {
        val row = sheet.createRow(row)
        val cell = row.createCell(cell)
        cell.setCellValue(value)
    }

    /**
     * 写入excel
     */
    fun writeExcel(fileName: String, list: MutableList<DebugDataBean.Parent>) {
        val result = ArrayList<DebugDataBean.Item>()
        list.forEach { parent ->
            if (parent.isExistedChildTab) {
                parent.childList.forEach { child ->
                    if (child.isExistedGroupTab) {
                        child.groupList.forEach { group ->
                            result.addAll(group.itemList)
                        }
                    } else {
                        result.addAll(child.itemList)
                    }
                }
            } else {
                result.addAll(parent.itemList)
            }
        }
        val workbook = HSSFWorkbook()
        val sheet = workbook.createSheet()
        for (i in result.indices) {
            val row = sheet.createRow(i)
            val cell0 = row.createCell(0)
            val cell1 = row.createCell(1)
            val cell2 = row.createCell(2)
            val cell3 = row.createCell(3)
            cell0.setCellValue(result[i].para0)
            cell1.setCellValue(result[i].para1)
            cell2.setCellValue(result[i].para5)
            cell3.setCellValue(result[i].revData)
        }
        val path = getFileBasePath()
        if (!File(path).exists()) {
            File(path).mkdirs()
        }
        val filePath = "${path}${fileName}.xls"
        // 5.生成一张表。03版本的工作簿是以.xls结尾
        val fileOutputStream = FileOutputStream(filePath)
        // 输出
        workbook.write(fileOutputStream)
        // 6.关闭流
        fileOutputStream.close()
        "表${filePath}生成成功！".logd(TAG)
    }

    class ExcelBean(
        val address: String, val itemName: String, val unit: String, val value: String
    ) {
        val isNegativeNumber: Boolean
            get() = unit.contains("-")
    }

    /**
     * 读取excel   （xls和xlsx）
     * @return
     */
    fun readExcel(fileName: String): ArrayList<ExcelBean> {
        val list = ArrayList<ExcelBean>()
        //1 通过assets 获取FileInputStream
//        val assetFileDescriptor: AssetFileDescriptor = context.assets.openFd(fileName)
//        val fileDescriptor: FileDescriptor = assetFileDescriptor.fileDescriptor
        val file = File(getFileBasePath() + fileName)
        val fileName = file.name
        val workbook = WorkbookFactory.create(file);
        val sheet = workbook.getSheetAt(0)
        //最大行数
        val rownum = sheet.physicalNumberOfRows
        //第一行有多少列数
//        val columns = sheet.getRow(0).physicalNumberOfCells
        for (i in 0 until rownum) {
            val row = sheet.getRow(i)
            list.add(
                ExcelBean(
                    row.getCell(0).toString(),
                    row.getCell(1).toString(),
                    row.getCell(2).toString(),
                    row.getCell(3).toString()
                )
            )

        }
        Log.e("readFile", "rownum:${rownum},fileName:${fileName}")
        return list
    }

    /**	获取单个单元格数据
     * @param cell
     * @return
     * @author lizixiang ,2018-05-08
     */
    private fun getCellFormatValue(cell: Cell?): Any? {
        var cellValue: Any? = null
        cellValue = if (cell != null) {
            // 判断cell类型
            when (cell.cellType) {
                Cell.CELL_TYPE_NUMERIC -> {
                    cell.numericCellValue.toString()
                }

                Cell.CELL_TYPE_FORMULA -> {
                    // 判断cell是否为日期格式
                    if (DateUtil.isCellDateFormatted(cell)) {
                        // 转换为日期格式YYYY-mm-dd
                        cell.dateCellValue
                    } else {
                        // 数字
                        cell.numericCellValue.toString()
                    }
                }

                Cell.CELL_TYPE_STRING -> {
                    cell.richStringCellValue.string
                }

                else -> ""
            }
        } else {
            ""
        }
        return cellValue
    }
}